package com.neo.test;

import com.neo.entity.Mytest;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 吧数据读取到list中
 * Created by n on 2015/4/29.
 */
public class InsertInfoToExcel {

    //该方法判断excel版本
    static Workbook openWorkbook(InputStream in, String filename) throws IOException {
        Workbook wb = null;
        if (filename.endsWith(".xlsx")) {
            wb = new XSSFWorkbook(in);//Excel 2007
        } else {
            wb = (Workbook) new HSSFWorkbook(in);//Excel 2003
        }
        return wb;
    }

    //该方法处理excel的数据，把第一列标记为id（递增），第9列标记为结果（默认是PASS），第10列标记为姓名
    public static void setExcelData(String fileName) throws Exception {
        List<Mytest> list=new ArrayList<>();
        InputStream in = new FileInputStream(fileName);    //创建输入流
        XSSFWorkbook hw = new XSSFWorkbook(new FileInputStream(
                //D:\WPS
                "E:\\CloudStation\\临时文件\\10 尚义一期50MW.xlsx"));
        XSSFSheet hsheet = hw.getSheet("计算过程");;// 获取文件的指定工作表m 默认的第一个Row row = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int totalRows = hsheet.getPhysicalNumberOfRows();    // 总行数
        int totalCells = hsheet.getPhysicalNumberOfRows();//总列数，根据第一行得来的
        System.out.println("列数:" + totalCells + " 行数：" + totalRows);

        //依次获取每一行
        for (int i = 1; i <= hsheet.getLastRowNum(); i++) {
            XSSFRow row = (XSSFRow) hsheet.getRow(i);// 获取行对象
            int rowNum = 0;
            try {
                rowNum = row.getRowNum();
            } catch (Exception e) {
               continue;
            }

            System.out.println("当前行:" + rowNum);

            int numberOfCells = row.getLastCellNum();//获取当前行的列数
            for (int c = 0; c < numberOfCells; c++) { // 总列(格)
            String lieming=    CellReference.convertNumToColString(c);
                System.err.println(lieming);
                Mytest mytest=new Mytest();
                Cell cell = row.getCell(c);
                if (cell == null) {
                    continue;
                } else {
                    int cellType = cell.getCellType();
                    mytest.setPosition(lieming+""+(i+1));
                    switch (cellType) {
                        case Cell.CELL_TYPE_STRING: // 代表文本
                            String stringCellValue = cell.getStringCellValue();
                            System.out.print(stringCellValue + "\t");
                            mytest.setComputer(stringCellValue);
                            break;
                        case Cell.CELL_TYPE_BLANK: // 空白格
                            String stringCellBlankValue = cell.getStringCellValue();
                            System.out.print(stringCellBlankValue + "\t");
                            break;
                        case Cell.CELL_TYPE_BOOLEAN: // 布尔型
                            boolean booleanCellValue = cell.getBooleanCellValue();
                            System.out.print(booleanCellValue + "\t");
                            break;
                        case Cell.CELL_TYPE_NUMERIC: // 数字||日期
                            boolean cellDateFormatted = DateUtil.isCellDateFormatted(cell);
                            if (cellDateFormatted) {
                                Date dateCellValue = cell.getDateCellValue();
                                System.out.print(sdf.format(dateCellValue) + "\t");
                            } else {
                                double numericCellValue = cell.getNumericCellValue();
                                System.out.print(numericCellValue + "\t");
                            }
                            break;
                        case Cell.CELL_TYPE_ERROR: // 错误
                            byte errorCellValue = cell.getErrorCellValue();
                            System.out.print(errorCellValue + "\t");
                            break;
                        case Cell.CELL_TYPE_FORMULA: // 公式
                            int cachedFormulaResultType = cell.getCachedFormulaResultType();
                            System.out.print(cachedFormulaResultType + "\t");
                            mytest.setComputer(String.valueOf(cachedFormulaResultType));
                            break;
                    }

                }
                list.add(mytest);
            }
        }
        //写入数据，关闭
        System.out.println(list);
        in.close();
    }
    public static void main(String[] args) throws Exception {
//        String fileName="E:"+ File.separator+"hello.txt";
        String fileName = "E:\\CloudStation\\临时文件\\10 尚义一期50MW.xlsx";
        setExcelData(fileName);
//        File f=new File(fileName);
//        if(f.exists())
//            System.out.println("new file successfully");
//        Writer out =new FileWriter(f);
//        String str="hello";
//        out.write(str);
//        out.close();
    }
}

